const { getConnection } = require('../config/db');

class VideoModel {
  /**
   * 批量获取视频的剧种信息（优化：解决 N+1 查询问题）
   */
  static async batchLoadRelations(connection, videoIds) {
    if (!videoIds || videoIds.length === 0) {
      return { genres: {} };
    }

    const placeholders = videoIds.map(() => '?').join(',');

    // 批量查询剧种
    const [genreRows] = await connection.execute(`
      SELECT vg.video_id, g.id, g.name
      FROM video_genres vg
      INNER JOIN genres g ON vg.genre_id = g.id
      WHERE vg.video_id IN (${placeholders})
      ORDER BY vg.video_id, g.id
    `, videoIds);

    // 组织数据
    const genres = {};

    genreRows.forEach(row => {
      if (!genres[row.video_id]) {
        genres[row.video_id] = [];
      }
      genres[row.video_id].push({ id: row.id, name: row.name });
    });

    return { genres };
  }

  /**
   * 为视频列表添加关联数据
   */
  static attachRelationsToVideos(videos, relations) {
    return videos.map(video => {
      const genres = relations.genres[video.id] || [];
      
      return {
        ...video,
        genres
      };
    });
  }
  
  // 创建视频
  static async create(videoData) {
    const connection = await getConnection();
    try {
      // 插入视频基本信息（已移除 area_id）
      const videoSql = `
        INSERT INTO videos 
        (id, title, description, source, copyright_type, video_url, cover_url) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `;
      
      const videoValues = [
        videoData.id,
        videoData.title,
        videoData.description,
        videoData.source,
        videoData.copyright_type,
        videoData.video_url,
        videoData.cover_url
      ];
      
      const [videoResult] = await connection.execute(videoSql, videoValues);
      
      // 插入视频剧种关联信息
      if (videoData.genre_ids && Array.isArray(videoData.genre_ids)) {
        for (const genreId of videoData.genre_ids) {
          const genreSql = `INSERT INTO video_genres (video_id, genre_id) VALUES (?, ?)`;
          await connection.execute(genreSql, [videoData.id, genreId]);
        }
      }
      
      return videoResult;
    } finally {
      connection.release();
    }
  }

  // 根据ID查找视频
  static async findById(id) {
    const connection = await getConnection();
    try {
      // 获取视频基本信息
      const videoSql = `
        SELECT v.* 
        FROM videos v 
        WHERE v.id = ?
      `;
      const [videoRows] = await connection.execute(videoSql, [id]);
      
      if (!videoRows || videoRows.length === 0) {
        return null;
      }
      
      const video = videoRows[0];
      
      // 获取视频关联的剧种信息
      const genreSql = `
        SELECT g.id, g.name
        FROM genres g
        INNER JOIN video_genres vg ON g.id = vg.genre_id
        WHERE vg.video_id = ?
      `;
      const [genreRows] = await connection.execute(genreSql, [id]);
      video.genres = genreRows;
      
      return video;
    } finally {
      connection.release();
    }
  }

  // 更新视频信息
  static async update(id, videoData) {
    const connection = await getConnection();
    try {
      // 更新视频基本信息
      const fields = [];
      const values = [];
      
      // 过滤掉 genre_ids 和 area_ids 字段，因为我们现在使用关联表
      Object.keys(videoData).forEach(key => {
        if (key !== 'id' && key !== 'genre_ids' && key !== 'area_ids') {
          fields.push(`${key} = ?`);
          values.push(videoData[key]);
        }
      });
      
      if (fields.length > 0) {
        values.push(id);
        
        const sql = `UPDATE videos SET ${fields.join(', ')} WHERE id = ?`;
        await connection.execute(sql, values);
      }
      
      // 更新视频剧种关联信息
      if (videoData.genre_ids && Array.isArray(videoData.genre_ids)) {
        // 先删除原有的关联信息
        const deleteSql = 'DELETE FROM video_genres WHERE video_id = ?';
        await connection.execute(deleteSql, [id]);
        
        // 插入新的关联信息
        for (const genreId of videoData.genre_ids) {
          const insertSql = 'INSERT INTO video_genres (video_id, genre_id) VALUES (?, ?)';
          await connection.execute(insertSql, [id, genreId]);
        }
      }
      
      return { affectedRows: 1 };
    } finally {
      connection.release();
    }
  }

  // 删除视频
  static async delete(id) {
    const connection = await getConnection();
    try {
      const sql = 'DELETE FROM videos WHERE id = ?';
      const [result] = await connection.execute(sql, [id]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 获取视频列表（分页）- 已优化：使用批量查询
  static async getList(page = 1, pageSize = 20, search = '') {
    const connection = await getConnection();
    try {
      const pageNum = Number.isFinite(Number(page)) ? Number(page) : 1;
      const pageSizeNum = Number.isFinite(Number(pageSize)) ? Number(pageSize) : 20;
      const offset = (pageNum - 1) * pageSizeNum;
      
      let sql = 'SELECT v.* FROM videos v';
      const countSql = 'SELECT COUNT(*) as total FROM videos v';
      const params = [];
      
      if (search) {
        sql += ' WHERE v.title LIKE ?';
        params.push(`%${search}%`);
      }
      
      sql += ` ORDER BY v.created_at DESC LIMIT ${pageSizeNum} OFFSET ${offset}`;
      
      // 获取视频列表
      const [rows] = await connection.execute(sql, params);
      
      // 获取总数
      const [countResult] = await connection.execute(
        countSql + (search ? ' WHERE v.title LIKE ?' : ''), 
        params
      );
      
      // 批量加载关联数据（优化：减少数据库查询）
      if (rows.length > 0) {
        const videoIds = rows.map(v => v.id);
        const relations = await this.batchLoadRelations(connection, videoIds);
        const videosWithRelations = this.attachRelationsToVideos(rows, relations);
        
        return {
          list: videosWithRelations,
          total: Number(countResult[0].total),
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        };
      }
      
      return {
        list: [],
        total: 0,
        page: parseInt(page),
        pageSize: parseInt(pageSize)
      };
    } finally {
      connection.release();
    }
  }

  // 根据剧种ID获取视频
  static async getByGenreId(genreId, page = 1, pageSize = 20) {
    const connection = await getConnection();
    try {
      const pageNum = Number.isFinite(Number(page)) ? Number(page) : 1;
      const pageSizeNum = Number.isFinite(Number(pageSize)) ? Number(pageSize) : 20;
      const offset = (pageNum - 1) * pageSizeNum;
      
      const sql = `
        SELECT v.* 
        FROM videos v 
        INNER JOIN video_genres vg ON v.id = vg.video_id
        WHERE vg.genre_id = ? 
        ORDER BY v.created_at DESC 
        LIMIT ${pageSizeNum} OFFSET ${offset}
      `;
      
      const [rows] = await connection.execute(sql, [genreId]);
      
      const countSql = `
        SELECT COUNT(*) as total 
        FROM videos v 
        INNER JOIN video_genres vg ON v.id = vg.video_id
        WHERE vg.genre_id = ?
      `;
      const [countResult] = await connection.execute(countSql, [genreId]);
      
      // 批量加载关联数据
      if (rows.length > 0) {
        const videoIds = rows.map(v => v.id);
        const relations = await this.batchLoadRelations(connection, videoIds);
        const videosWithRelations = this.attachRelationsToVideos(rows, relations);
        
        return {
          list: videosWithRelations,
          total: Number(countResult[0].total),
          page: parseInt(String(pageNum)),
          pageSize: parseInt(String(pageSizeNum))
        };
      }
      
      return {
        list: [],
        total: 0,
        page: parseInt(String(pageNum)),
        pageSize: parseInt(String(pageSizeNum))
      };
    } finally {
      connection.release();
    }
  }

  // 增加播放次数
  static async incrementPlayCount(id) {
    const connection = await getConnection();
    try {
      const sql = 'UPDATE videos SET play_count = play_count + 1 WHERE id = ?';
      const [result] = await connection.execute(sql, [id]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 搜索视频 - 已优化
  static async search(keyword, page = 1, pageSize = 20) {
    const connection = await getConnection();
    try {
      const pageNum = Number.isFinite(Number(page)) ? Number(page) : 1;
      const pageSizeNum = Number.isFinite(Number(pageSize)) ? Number(pageSize) : 20;
      const offset = (pageNum - 1) * pageSizeNum;
      
      const sql = `
        SELECT v.* 
        FROM videos v 
        WHERE v.title LIKE ? 
        ORDER BY v.play_count DESC, v.created_at DESC 
        LIMIT ${pageSizeNum} OFFSET ${offset}
      `;
      
      const [rows] = await connection.execute(sql, [`%${keyword}%`]);
      
      const countSql = 'SELECT COUNT(*) as total FROM videos WHERE title LIKE ?';
      const [countResult] = await connection.execute(countSql, [`%${keyword}%`]);
      
      // 批量加载关联数据
      if (rows.length > 0) {
        const videoIds = rows.map(v => v.id);
        const relations = await this.batchLoadRelations(connection, videoIds);
        const videosWithRelations = this.attachRelationsToVideos(rows, relations);
        
        return {
          list: videosWithRelations,
          total: Number(countResult[0].total),
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        };
      }
      
      return {
        list: [],
        total: 0,
        page: parseInt(page),
        pageSize: parseInt(pageSize)
      };
    } finally {
      connection.release();
    }
  }

  // 获取推荐视频（按播放量排序）- 已优化
  static async getRecommended(page = 1, pageSize = 20) {
    const connection = await getConnection();
    try {
      const pageNum = Number.isFinite(Number(page)) ? Number(page) : 1;
      const pageSizeNum = Number.isFinite(Number(pageSize)) ? Number(pageSize) : 20;
      const offset = (pageNum - 1) * pageSizeNum;
      
      const sql = `
        SELECT v.* 
        FROM videos v 
        ORDER BY v.play_count DESC 
        LIMIT ${pageSizeNum} OFFSET ${offset}
      `;
      
      const [rows] = await connection.execute(sql);
      
      const countSql = 'SELECT COUNT(*) as total FROM videos';
      const [countResult] = await connection.execute(countSql);
      
      // 批量加载关联数据
      if (rows.length > 0) {
        const videoIds = rows.map(v => v.id);
        const relations = await this.batchLoadRelations(connection, videoIds);
        const videosWithRelations = this.attachRelationsToVideos(rows, relations);
        
        return {
          list: videosWithRelations,
          total: Number(countResult[0].total),
          page: parseInt(String(pageNum)),
          pageSize: parseInt(String(pageSizeNum))
        };
      }
      
      return {
        list: [],
        total: 0,
        page: parseInt(String(pageNum)),
        pageSize: parseInt(String(pageSizeNum))
      };
    } finally {
      connection.release();
    }
  }
}

module.exports = VideoModel;

